use chrono::{Datelike, Days, Months, NaiveDate};
use clickhouse_rs::ClientHandle;

use crate::base::get_ch_pool;

pub struct IndiClieAcbl<'a> {
    back_date: &'a NaiveDate,
}
impl <'a> IndiClieAcbl<'a> {
    pub fn new(back_date: &'a NaiveDate) -> Self {
        Self {back_date}
    }
    async fn collapsing(&self,client:&mut ClientHandle,sdate: &NaiveDate,edate: &NaiveDate) -> anyhow::Result<()> {
        let sql = format!(r#"insert into TMP_INDI_CLIE_ACBL_BACK(DTID,CUID,UTID,CHGD,KHID,CXBL,BBLCBL,FBBLCBL,JJBL,GZBL,SGBL,DSFDXBL,ZCVL,`DIVI.EMID`,`DIVI.DPID`,`DIVI.DSVL`,SIGN)
        with toDate('{}') AS SDATE,toDate('{}') AS EDATE
        select DTID,CUID,anyLast(UTID),anyLast(CHGD),KHID,anyLast(CXBL),anyLast(BBLCBL),anyLast(FBBLCBL),anyLast(JJBL),
        anyLast(GZBL),anyLast(SGBL),anyLast(DSFDXBL),anyLast(ZCVL),anyLast(`DIVI.EMID`),anyLast(`DIVI.DPID`),anyLast(`DIVI.DSVL`),-1
        from T_INDI_CLIE_ACBL
        where DTID>=SDATE AND DTID<=EDATE AND CUID in (SELECT CUID FROM JOIN_INDI_ACCO_DIVI_BACK)
        group by DTID,KHID,CUID
        having sum(SIGN)>0"#, sdate.format("%Y%m%d").to_string(),edate.format("%Y%m%d"));
        client.execute(&sql).await?;
        Ok(())
    }
    async fn insert(&self,client:&mut ClientHandle,sdate: &NaiveDate,edate: &NaiveDate) -> anyhow::Result<()> {
        let sql = format!(r#"insert into TMP_INDI_CLIE_ACBL_BACK(DTID,CUID,UTID,CHGD,KHID,CXBL,BBLCBL,FBBLCBL,JJBL,GZBL,SGBL,DSFDXBL,ZCVL,`DIVI.EMID`,`DIVI.DPID`,`DIVI.DSVL`,SIGN)
        with toDate('{}') AS SDATE,toDate('{}') AS EDATE,
            joinGet('JOIN_INDI_CLIE_DIVI','DIVI.DPID',DTID,CUID) AS DPIDS,
            joinGet('JOIN_INDI_CLIE_DIVI','DIVI.EMID',DTID,CUID) AS EMIDS,
            joinGet('JOIN_INDI_CLIE_DIVI','DIVI.DSVL',DTID,CUID) AS DSVLS,
        select DTID,CUID,anyLast(UTID),anyLast(CHGD),KHID,anyLast(CXBL),anyLast(BBLCBL),anyLast(FBBLCBL),anyLast(JJBL),
        anyLast(GZBL),anyLast(SGBL),anyLast(DSFDXBL),anyLast(ZCVL),DPIDS,EMIDS,DSVLS,1
        from T_INDI_CLIE_ACBL
        where DTID>=SDATE AND DTID<=EDATE AND CUID in (SELECT CUID FROM JOIN_INDI_ACCO_DIVI_BACK)
        group by DTID,KHID,CUID
        having sum(SIGN)>0"#, sdate.format("%Y%m%d").to_string(),edate.format("%Y%m%d"));
        client.execute(&sql).await?;
        Ok(())
    }
    async fn create_join(&self,client:&mut ClientHandle,sdate: &NaiveDate,edate: &NaiveDate) -> anyhow::Result<()> {
        let sql="truncate table JOIN_INDI_CLIE_DIVI";
        client.execute(sql).await?;
        let sql=format!(r#"INSERT INTO JOIN_INDI_CLIE_DIVI(DTID,CUID,DIVI.DPID,DIVI.EMID,DIVI.DSVL)
        WITH toDate('{}') AS SDATE,toDate('{}') AS EDATE
        SELECT A.DTID,A.CUID,groupArray(B.DPID) DPIDS,groupArray(B.EMID) EMIDS,groupArray(B.ACBL/A.ACBL) DSVLS FROM
        (SELECT DTID,CUID,SUM(ACBL) ACBL FROM
            (SELECT DTID,CUID,SUM(ACBL1) ACBL FROM DF_INDI_ACCO_ACBL WHERE DTID>=SDATE AND DTID<=EDATE AND ACID IN (SELECT ACID FROM JOIN_COMP_ACCO_DIVI_BACK) 
            GROUP BY DTID,CUID HAVING ACBL<>0
            UNION ALL
            SELECT DTID,CUID,SUM(ACBL1) ACBL FROM DF_INDI_FANA_ACBL WHERE DTID>=SDATE AND DTID<=EDATE AND ACID IN (SELECT ACID FROM JOIN_COMP_ACCO_DIVI_BACK) 
            GROUP BY DTID,CUID HAVING ACBL<>0)
        GROUP BY DTID,CUID
        ) A INNER JOIN
        (SELECT DTID,CUID,DPID,EMID,SUM(ACBL) ACBL FROM
            (SELECT DTID,CUID,DPID,EMID,SUM(ACBL1) ACBL FROM DF_INDI_ACCO_ACBL WHERE DTID>=SDATE AND DTID<=EDATE AND ACID IN (SELECT ACID FROM JOIN_COMP_ACCO_DIVI_BACK) GROUP BY DTID,CUID,DPID,EMID HAVING ACBL<>0
            UNION ALL
            SELECT DTID,CUID,DPID,EMID,SUM(ACBL1) ACBL FROM DF_INDI_FANA_ACBL WHERE DTID>=SDATE AND DTID<=EDATE AND ACID IN (SELECT ACID FROM JOIN_COMP_ACCO_DIVI_BACK) GROUP BY DTID,CUID,DPID,EMID HAVING ACBL<>0)
        GROUP BY DTID,CUID,DPID,EMID
        ) B ON A.DTID=B.DTID AND A.CUID=B.CUID
        GROUP BY A.DTID,A.CUID;"#,sdate.format("%Y%m%d").to_string(),edate.format("%Y%m%d").to_string());
        client.execute(sql).await?;
        Ok(())
    }
    pub (super) async fn run(&self) -> anyhow::Result<()> {
        let sql = format!("select max(DTID) from T_INDI_CLIE_ACBL");
        let mut client = get_ch_pool().get_handle().await?;
        let b = client.query(&sql).fetch_all().await?;
        let max_date: NaiveDate = b.rows().next().unwrap().get(0)?;
        let mut curr_date =
            NaiveDate::from_ymd_opt(self.back_date.year(), self.back_date.month(), 1).unwrap();
        let sql="CREATE TABLE IF NOT EXISTS TMP_INDI_CLIE_ACBL_BACK AS T_INDI_CLIE_ACBL";
            client.execute(&sql).await?;
        while curr_date <= max_date {
            let sdate = if curr_date > *self.back_date {
                &curr_date
            } else {
                self.back_date
            };
            let sql="TRUNCATE TABLE TMP_INDI_CLIE_ACBL_BACK";
            client.execute(&sql).await?;
            let edate = curr_date
                .checked_add_months(Months::new(1))
                .unwrap()
                .checked_sub_days(Days::new(1))
                .unwrap();
            self.create_join(&mut client, sdate, &edate).await?;
            self.collapsing(&mut client, sdate, &edate).await?;
            self.insert(&mut client, sdate, &edate).await?;
            let sql="INSERT INTO T_INDI_CLIE_ACBL SELECT * FROM TMP_INDI_CLIE_ACBL_BACK ORDER BY SIGN";
            client.execute(&sql).await?;
            curr_date = curr_date.checked_add_months(Months::new(1)).unwrap();
        }
        let sql="DROP TABLE TMP_INDI_CLIE_ACBL_BACK";
        client.execute(&sql).await?;
        Ok(())
    }
}
